﻿Imports DTO
Imports System.Data.OleDb
Public Class LopHocDAO
    Inherits Connect

    Public Sub New()

    End Sub

    Public Function LayDSLopHoc() As DataTable
        Dim dt As New DataTable
        Dim cn As OleDbConnection
        cn = ConnectionData()
        Dim sql As String
        sql = "Select MaLopHoc, TenLopHoc from LopHoc order by MaLopHoc"
        Dim da As OleDbDataAdapter
        Dim ds As New DataSet()
        da = New OleDbDataAdapter(sql, cn)
        da.Fill(dt)
        dt.Columns.Add("STT", GetType(Integer))
        For i As Integer = 0 To dt.Rows.Count - 1
            dt.Rows(i).Item("STT") = i + 1
        Next
        cn.Close()
        Return dt
    End Function

    Public Sub ThemRBLH(ByVal dto As LopHocDTO)
        Dim cn As OleDbConnection = Me.ConnectionData()
        Dim sql As String
        dto.Marangbuoc = TaoMaRBLH()
        Dim cmd As OleDbCommand
        sql = "Insert into RangBuocLopHoc(MaRangBuocLopHoc, MaLopHoc, Thu, TietHoc, TrangThai)" & _
      " values('" & dto.Marangbuoc & "','" & dto.MalopRblh & "', " & _
      dto.Thu & "," & dto.Tiethoc & "," & dto.Trangthai & ")"
        cmd = New OleDbCommand(sql, cn)
        cmd.ExecuteNonQuery()
        cn.Close()
    End Sub

    Public Sub ThemLop(ByVal LopHocdto As LopHocDTO)
        Dim cn As OleDbConnection = Me.ConnectionData()
        Dim sql As String
        Dim cmd As OleDbCommand
        Dim MaLop As String = LayMa("LopHoc", "MaLopHoc")
        sql = "Insert into LopHoc(MalopHoc, TenLopHoc, MaKhoi) values( '" & _
LopHocdto.MaLopLh & "','" & LopHocdto.TenLopLh & "'," & " '" & LopHocdto.MaKhoi & "' )"
        cmd = New OleDbCommand(sql, cn)
        cmd.ExecuteNonQuery()
        cn.Close()
    End Sub

    Public Function CapNhat(ByVal LopDTO As LopHocDTO) As DataTable
        Dim dt As New DataTable
        Dim cn As OleDbConnection
        cn = Me.ConnectionData()
        Dim sql As String
        sql = "update lophoc set TenLopHoc='" + LopDTO.TenLopLh + "' "
        sql += "where MaLopHoc='" + LopDTO.MaLopLh + "'"
        Dim da As New OleDbDataAdapter(sql, cn)
        da.Fill(dt)
        cn.Close()
        Return dt
    End Function

    Public Function LayDSLopHoc(ByVal makhoi As String) As DataTable
        Dim dt As New DataTable
        Dim cn As OleDbConnection
        cn = ConnectionData()
        Dim sql As String
        sql = "Select MaLopHoc, TenLopHoc from LopHoc where MaKhoi='" + makhoi + "'"
        Dim da As OleDbDataAdapter
        da = New OleDbDataAdapter(sql, cn)
        da.Fill(dt)
        dt.Columns.Add("STT", GetType(Integer))
        For i As Integer = 0 To dt.Rows.Count - 1
            dt.Rows(i).Item("STT") = i + 1
        Next
        cn.Close()
        Return dt
    End Function

    Public Sub XoaLopHoc(ByVal LopDTO As LopHocDTO)
        Dim cn As New OleDbConnection
        cn = Me.ConnectionData()
        Dim sql As String
        sql = "delete from Lophoc where MaLopHoc='" + LopDTO.MaLopLh + "'"
        Dim cmd As New OleDbCommand(sql, cn)
        cmd.ExecuteNonQuery()
        cn.Close()
    End Sub

    Public Sub XoaRBLH(ByVal dto As LopHocDTO)
        Dim cn As New OleDbConnection
        cn = Me.ConnectionData
        Dim sql As String
        sql = "Delete from RangBuocLopHoc where MaLopHoc='" & dto.MalopRblh & "'"
        Dim cmd As New OleDbCommand(sql, cn)
        cmd.ExecuteNonQuery()
        cn.Close()
    End Sub

#Region "Lấy lịch rảnh"
    Function TietHoc() As List(Of String)
        Dim list As New List(Of String)
        Dim sql As String = "select tiethoc from rangbuoclophoc group by tiethoc"
        Dim cn As New OleDbConnection
        cn = Me.ConnectionData
        Dim cmd As New OleDbCommand(sql, cn)
        Dim dr As OleDbDataReader
        dr = cmd.ExecuteReader
        While dr.Read
            list.Add(dr.GetValue(0).ToString)
        End While
        cn.Close()
        Return list
    End Function

    Function Thu2(ByVal dto As LopHocDTO) As List(Of String)
        Dim list As New List(Of String)
        Dim sql As String = "SELECT trangthai " & _
       "FROM RangBuocLopHoc " & _
"WHERE malophoc='" & dto.MalopRblh & "' AND thu=2"

        Dim cn As New OleDbConnection
        cn = Me.ConnectionData
        Dim cmd As New OleDbCommand(sql, cn)
        Dim dr As OleDbDataReader
        dr = cmd.ExecuteReader
        While dr.Read
            list.Add(dr.GetValue(0).ToString)
        End While
        cn.Close()
        Return list
    End Function

    Function Thu3(ByVal dto As LopHocDTO) As List(Of String)
        Dim list As New List(Of String)
        Dim sql As String = "SELECT trangthai " & _
       "FROM RangBuocLopHoc " & _
"WHERE malophoc='" & dto.MalopRblh & "' AND thu=3"

        Dim cn As New OleDbConnection
        cn = Me.ConnectionData
        Dim cmd As New OleDbCommand(sql, cn)
        Dim dr As OleDbDataReader
        dr = cmd.ExecuteReader
        While dr.Read
            list.Add(dr.GetValue(0).ToString)
        End While
        cn.Close()
        Return list
    End Function

    Function Thu4(ByVal dto As LopHocDTO) As List(Of String)
        Dim list As New List(Of String)
        Dim sql As String = "SELECT trangthai " & _
       "FROM RangBuocLopHoc " & _
"WHERE malophoc='" & dto.MalopRblh & "' AND thu=4"

        Dim cn As New OleDbConnection
        cn = Me.ConnectionData
        Dim cmd As New OleDbCommand(sql, cn)
        Dim dr As OleDbDataReader
        dr = cmd.ExecuteReader
        While dr.Read
            list.Add(dr.GetValue(0).ToString)
        End While
        cn.Close()
        Return list
    End Function

    Function Thu5(ByVal dto As LopHocDTO) As List(Of String)
        Dim list As New List(Of String)
        Dim sql As String = "SELECT trangthai " & _
       "FROM RangBuocLopHoc " & _
"WHERE malophoc='" & dto.MalopRblh & "' AND thu=5"

        Dim cn As New OleDbConnection
        cn = Me.ConnectionData
        Dim cmd As New OleDbCommand(sql, cn)
        Dim dr As OleDbDataReader
        dr = cmd.ExecuteReader
        While dr.Read
            list.Add(dr.GetValue(0).ToString)
        End While
        cn.Close()
        Return list
    End Function

    Function Thu6(ByVal dto As LopHocDTO) As List(Of String)
        Dim list As New List(Of String)
        Dim sql As String = "SELECT trangthai " & _
       "FROM RangBuocLopHoc " & _
"WHERE malophoc='" & dto.MalopRblh & "' AND thu=6"

        Dim cn As New OleDbConnection
        cn = Me.ConnectionData
        Dim cmd As New OleDbCommand(sql, cn)
        Dim dr As OleDbDataReader
        dr = cmd.ExecuteReader
        While dr.Read
            list.Add(dr.GetValue(0).ToString)
        End While
        cn.Close()
        Return list
    End Function

    Function Thu7(ByVal dto As LopHocDTO) As List(Of String)
        Dim list As New List(Of String)
        Dim sql As String = "SELECT trangthai " & _
       "FROM RangBuocLopHoc " & _
"WHERE malophoc='" & dto.MalopRblh & "' AND thu=7"

        Dim cn As New OleDbConnection
        cn = Me.ConnectionData
        Dim cmd As New OleDbCommand(sql, cn)
        Dim dr As OleDbDataReader
        dr = cmd.ExecuteReader
        While dr.Read
            list.Add(dr.GetValue(0).ToString)
        End While
        cn.Close()
        Return list
    End Function

    Function Thu8(ByVal dto As LopHocDTO) As List(Of String)
        Dim list As New List(Of String)
        Dim sql As String = "SELECT trangthai " & _
       "FROM RangBuocLopHoc " & _
"WHERE malophoc='" & dto.MalopRblh & "' AND thu=8"

        Dim cn As New OleDbConnection
        cn = Me.ConnectionData
        Dim cmd As New OleDbCommand(sql, cn)
        Dim dr As OleDbDataReader
        dr = cmd.ExecuteReader
        While dr.Read
            list.Add(dr.GetValue(0).ToString)
        End While
        cn.Close()
        Return list
    End Function

    Public Function LayLichRanh(ByVal dto As LopHocDTO) As DataTable
        Dim dt As New DataTable
        dt.Columns.Add("TietHoc", GetType(Byte))
        dt.Columns.Add("thu2", GetType(String))
        dt.Columns.Add("thu3", GetType(String))
        dt.Columns.Add("thu4", GetType(String))
        dt.Columns.Add("thu5", GetType(String))
        dt.Columns.Add("thu6", GetType(String))
        dt.Columns.Add("thu7", GetType(String))
        dt.Columns.Add("thu8", GetType(String))

        Dim tiet As New List(Of String)
        tiet = TietHoc()
        Dim arrthu2 As New List(Of String)
        Dim arrthu3 As New List(Of String)
        Dim arrthu4 As New List(Of String)
        Dim arrthu5 As New List(Of String)
        Dim arrthu6 As New List(Of String)
        Dim arrthu7 As New List(Of String)
        Dim arrthu8 As New List(Of String)

        arrthu2 = Thu2(dto)
        arrthu3 = Thu3(dto)
        arrthu4 = Thu4(dto)
        arrthu5 = Thu5(dto)
        arrthu6 = Thu6(dto)
        arrthu7 = Thu7(dto)
        arrthu8 = Thu8(dto)
        For i As Integer = 0 To tiet.Count - 1
            dt.Rows.Add()
            dt.Rows(i).Item(0) = tiet(i).ToString
            dt.Rows(i).Item(1) = arrthu2(i).ToString
            dt.Rows(i).Item(2) = arrthu3(i).ToString
            dt.Rows(i).Item(3) = arrthu4(i).ToString
            dt.Rows(i).Item(4) = arrthu5(i).ToString
            dt.Rows(i).Item(5) = arrthu6(i).ToString
            dt.Rows(i).Item(6) = arrthu7(i).ToString
            dt.Rows(i).Item(7) = arrthu8(i).ToString
        Next
        Return dt
    End Function
#End Region

    Sub CapNhatLichRanh(ByVal dto As LopHocDTO, ByVal thu As Byte, ByVal tiet As Byte, ByVal trangthai As Byte)
        Dim cn As New OleDbConnection
        cn = Me.ConnectionData
        Dim sql As String = "Update RangBuocLopHoc SET trangthai=" & trangthai & _
        " where thu=" & thu & " and tiethoc=" & tiet & " and malophoc='" & dto.MalopRblh & "'"
        Dim cmd As New OleDbCommand(sql, cn)
        cmd.ExecuteNonQuery()
        cn.Close()
    End Sub

    Function TaoMaLopHoc() As String
        Return LayMa("LopHoc", "MaLopHoc")
    End Function

    Function TaoMaRBLH() As String
        Return LayMa("RangBuocLopHoc", "MaRangBuocLopHoc")
        'Dim marb As String
        'Dim dt As New DataTable
        'Dim cn As New OleDbConnection
        'cn = Me.ConnectionData
        'Dim sql As String = "select marangbuoclophoc from rangbuoclophoc"
        'Dim cmd As New OleDbCommand(sql, cn)
        'Dim list As New List(Of String)
        'Dim dr As OleDbDataReader = cmd.ExecuteReader()
        'While dr.Read
        '    list.Add(dr.GetString(0))
        'End While
        'Dim sldong As New Integer
        'sldong = list.Count - 1
        'list.Sort()
        'Dim macuoi As String = list(sldong).ToString
        'Dim kytu As String = macuoi.Substring(0, 4)
        'Dim socuoi As String = macuoi.Substring(4)
        'Dim so As Integer = (CType(socuoi, Integer) + 1)
        'Dim somoi As String = String.Format("{0:D5}", so)
        'marb = kytu + somoi
        'Return marb
    End Function
End Class
